/***********************************************************************************************************************************
 * Name:	Wait_Statistics.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Create the schema to analyze wait statistics on the monitoring host.
 	 DROP PROCEDURE dbo.WaitsPivotData;
	 DROP FUNCTION dbo.fWaitsAnalysis;
	 DROP TABLE dbo.SQLWaitStats;
	 DROP SEQUENCE dbo.SN_SQLWaitStats;
	 DROP TABLE dbo.BenignWaits;
**/
USE DBAdmin;
GO


--==================================================================================================================================
IF OBJECT_ID('dbo.BenignWaits') IS Null CREATE TABLE dbo.BenignWaits (wait_type NVARCHAR(64) NOT Null, CONSTRAINT PK_BenignWaits PRIMARY KEY CLUSTERED (wait_type));
ELSE TRUNCATE TABLE dbo.BenignWaits;
INSERT INTO dbo.BenignWaits (wait_type) VALUES
	(N'BROKER_EVENTHANDLER'),
	(N'BROKER_RECEIVE_WAITFOR'),
	(N'BROKER_TASK_STOP'),
	(N'BROKER_TO_FLUSH'),
	(N'BROKER_TRANSMITTER'),
	(N'CHECKPOINT_QUEUE'),
	(N'CHKPT'),
	(N'CLR_AUTO_EVENT'),
	(N'CLR_MANUAL_EVENT'),
	(N'CLR_SEMAPHORE'),
	(N'DBMIRROR_DBM_EVENT'),
	(N'DBMIRROR_EVENTS_QUEUE'),
	(N'DBMIRROR_WORKER_QUEUE'),
	(N'DBMIRRORING_CMD'),
	(N'DIRTY_PAGE_POLL'),
	(N'DISPATCHER_QUEUE_SEMAPHORE'),
	(N'EXECSYNC'),
	(N'FSAGENT'),
	(N'FT_IFTS_SCHEDULER_IDLE_WAIT'),
	(N'FT_IFTSHC_MUTEX'),
	(N'HADR_CLUSAPI_CALL'),
	(N'HADR_FILESTREAM_IOMGR_IOCOMPLETION'),
	(N'HADR_LOGCAPTURE_WAIT'),
	(N'HADR_NOTIFICATION_DEQUEUE'),
	(N'HADR_TIMER_TASK'),
	(N'HADR_WORK_QUEUE'),
	(N'KSOURCE_WAKEUP'),
	(N'LAZYWRITER_SLEEP'),
	(N'LOGMGR_QUEUE'),
	(N'ONDEMAND_TASK_QUEUE'),
	(N'PWAIT_ALL_COMPONENTS_INITIALIZED'),
	(N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'),
	(N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'),
	(N'REQUEST_FOR_DEADLOCK_SEARCH'),
	(N'RESOURCE_QUEUE'),
	(N'SERVER_IDLE_CHECK'),
	(N'SLEEP_BPOOL_FLUSH'),
	(N'SLEEP_DBSTARTUP'),
	(N'SLEEP_DCOMSTARTUP'),
	(N'SLEEP_MASTERDBREADY'),
	(N'SLEEP_MASTERMDREADY'),
	(N'SLEEP_MASTERUPGRADED'),
	(N'SLEEP_MSDBSTARTUP'),
	(N'SLEEP_SYSTEMTASK'),
	(N'SLEEP_TASK'),
	(N'SLEEP_TEMPDBSTARTUP'),
	(N'SNI_HTTP_ACCEPT'),
	(N'SP_SERVER_DIAGNOSTICS_SLEEP'),
	(N'SQLTRACE_BUFFER_FLUSH'),
	(N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'),
	(N'SQLTRACE_WAIT_ENTRIES'),
	(N'TRACEWRITE'),
	(N'WAIT_FOR_RESULTS'),
	(N'WAIT_XTP_CKPT_CLOSE'),
	(N'WAIT_XTP_HOST_WAIT'),
	(N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG'),
	(N'WAITFOR'),
	(N'WAITFOR_TASKSHUTDOWN'),
	(N'XE_DISPATCHER_JOIN'),
	(N'XE_DISPATCHER_WAIT'),
	(N'XE_TIMER_EVENT');
GO


--==================================================================================================================================
IF OBJECT_ID(N'dbo.SN_SQLWaitStats') IS Null CREATE SEQUENCE dbo.SN_SQLWaitStats AS INTEGER START WITH 1 INCREMENT BY 1 MINVALUE 1;
IF OBJECT_ID(N'dbo.SQLWaitStats') IS Null CREATE TABLE dbo.SQLWaitStats (
	server_name			SYSNAME				NOT Null,
	server_time			DATETIMEOFFSET(0)	NOT Null,
	wait_type			NVARCHAR(64)		NOT Null,
	waiting_tasks_count	BIGINT				NOT Null,
	wait_time_ms		BIGINT				NOT Null,
	max_wait_time_ms	BIGINT				NOT Null,
	signal_wait_time_ms	BIGINT				NOT Null,
	row_id				INT					NOT Null CONSTRAINT DF_SQLWaitStats_RowID DEFAULT NEXT VALUE FOR dbo.SN_SQLWaitStats CONSTRAINT UQ_SQLWaitStats_RowID UNIQUE,
	CONSTRAINT PK_SQLWaitStats PRIMARY KEY CLUSTERED (server_name,server_time,wait_type));
GO


--==================================================================================================================================
IF OBJECT_ID(N'dbo.fWaitsAnalysis') IS Null EXECUTE (N'CREATE FUNCTION dbo.fWaitsAnalysis () RETURNS TABLE AS RETURN SELECT 0 AS Zero;')
GO
ALTER FUNCTION fWaitsAnalysis (@server_name SYSNAME, @localtime_from DATETIME2= '0001-01-01 00:00:00.0000000', @localtime_thru DATETIME2= '9999-12-31 23:59:59.9999999') RETURNS TABLE AS RETURN (
WITH
  Interval AS (SELECT
	  elapsed_time	= NullIF(DATEDIFF(ms,   FIRST_VALUE(server_time        ) OVER (PARTITION BY server_name, w.wait_type ORDER BY server_time ROWS 1 PRECEDING), server_time), 0),
	  waiting_tasks	= waiting_tasks_count - FIRST_VALUE(waiting_tasks_count) OVER (PARTITION BY server_name, w.wait_type ORDER BY server_time ROWS 1 PRECEDING),
	  wait_ms		= wait_time_ms -        FIRST_VALUE(wait_time_ms       ) OVER (PARTITION BY server_name, w.wait_type ORDER BY server_time ROWS 1 PRECEDING),
	  signal_ms		= signal_wait_time_ms - FIRST_VALUE(signal_wait_time_ms) OVER (PARTITION BY server_name, w.wait_type ORDER BY server_time ROWS 1 PRECEDING),
	  local_time	= CAST(SWITCHOFFSET(server_time, DATEPART(tz,SYSDATETIMEOFFSET())) AS DATETIME2(0)),
	  row_id
	FROM dbo.SQLWaitStats w LEFT JOIN dbo.BenignWaits b ON (w.wait_type = b.wait_type)
	  WHERE b.wait_type IS Null AND w.server_name = @server_name
	  ),
  Pentile (wait_pentile, row_id) AS (SELECT NTILE(5) OVER (PARTITION BY server_name, server_time ORDER BY wait_ms DESC), i.row_id
	FROM Interval i INNER JOIN dbo.SQLWaitStats w ON i.row_id = w.row_id)
SELECT
	  [Server Name]		= server_name,
	  [Local Time]		= local_time,
	  [Time Rank]		= DENSE_RANK() OVER (PARTITION BY server_name ORDER BY server_time DESC),
	  [Elapsed Time]	= CAST(DATEADD(ms, elapsed_time, '00:00:00') AS TIME(0)),
	  [Wait Type]		= wait_type,
	  [Wait Rank]		= DENSE_RANK() OVER (PARTITION BY server_name, server_time ORDER BY wait_ms DESC),
	  [Waiting Tasks]	= waiting_tasks,
	  [Wait Time]		= wait_ms,
	  [Signal Wait]		= signal_ms,
	  [Resource Wait]	= wait_ms - signal_ms,
	  [% of Total]		= CAST(100.0 * wait_ms /NullIF(SUM(wait_ms) OVER (PARTITION BY server_name, server_time), 0) AS DECIMAL(5,2)),
	  [Wait Weight]		= CAST(  1.0 * wait_ms /NullIF(elapsed_time, 0) AS FLOAT),
	  [Wait/Task]		= CASE waiting_tasks WHEN 0 THEN wait_ms ELSE wait_ms/waiting_tasks END
	FROM dbo.SQLWaitStats w INNER JOIN Interval i ON (w.row_id = i.row_id) INNER JOIN Pentile p ON (w.row_id = p.row_id)
	WHERE elapsed_time IS NOT Null AND wait_pentile = 1 AND 0 < wait_ms AND local_time BETWEEN @localtime_from AND @localtime_thru
);
GO


--==================================================================================================================================
IF OBJECT_ID(N'dbo.WaitsPivotData') IS Null EXECUTE (N'CREATE PROCEDURE dbo.WaitsPivotData AS RETURN 0;')
GO
ALTER PROCEDURE dbo.WaitsPivotData(@server_name SYSNAME, @localtime_from DATETIME2= '0001-01-01 00:00:00.0000000', @localtime_thru DATETIME2= '9999-12-31 23:59:59.9999999', @wait_weight FLOAT= 0.0) AS
SET NOCOUNT ON;
DECLARE @pivot TABLE (
	[Legend (Wait Type)]	NVARCHAR(64)	NOT Null,
	[Axis (Local Time)]		DATETIME2(0)	NOT Null,
	[Values (Wait Time)]	BIGINT			NOT Null);
INSERT INTO @pivot SELECT [Wait Type], [Local Time], [Wait Time]
  FROM dbo.fWaitsAnalysis(@server_name, @localtime_from, @localtime_thru) WHERE @wait_weight <= [Wait Weight];
SET NOCOUNT OFF;
SELECT [Legend (Wait Type)], SUM([Values (Wait Time)]) AS [Total Wait Time] FROM @pivot GROUP BY [Legend (Wait Type)] ORDER BY [Total Wait Time] DESC;
SELECT * FROM @pivot ORDER BY [Axis (Local Time)], [Values (Wait Time)] DESC, [Legend (Wait Type)];
GO